package com.sysmind.ravi.margin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.sysmind.ravi.margin.dao.CommonDao;
import com.sysmind.ravi.margin.dao.UserDao;
import com.sysmind.ravi.margin.model.User;
import com.sysmind.ravi.margin.utils.JDBCHelper;

public class UserDaoImpl implements UserDao
{	Connection conn = null;
	Connection conn1 = null;
	PreparedStatement pstmt = null;
	Statement stmt = null;
	JDBCHelper jhelp = null;
	JDBCHelper jhelp1 = null;
	ResultSet rs = null;
	CommonDao commonDao = null;

	@Override
	public boolean addUser(User user) 
	{	if(userExists(user.getEmailId()) != true)
		{	jhelp = new JDBCHelper();
			conn = jhelp.getConnection();
			
			String sql1 = " select user_seq.nextval as user_value from dual";
			String sql2 = "Insert into userinfo values(?,?,?,?,?,?)";
			try 
			{	
				conn.setAutoCommit(false);
				rs = conn.createStatement().executeQuery(sql1);
				
				while(rs.next())
				{	
					pstmt = conn.prepareStatement(sql2);
					int userId = rs.getInt("user_value");
					//System.out.println(userId);
					//pstmt.setInt(arg0, arg1)
					pstmt.setInt(1, userId);					
					pstmt.setString(2, user.getEmailId().toLowerCase());
					pstmt.setString(3,user.getPassword());
					pstmt.setString(4,user.getFirstName());
					pstmt.setString(5, user.getLastName());
					pstmt.setInt(6, 1);
					pstmt.executeUpdate();
					conn.commit();
					conn.setAutoCommit(true);
				}
				return true;
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}		
			finally
			{
				sql1 = null;
				sql2 = null;
				try 
				{	
					pstmt.close();
					pstmt = null;
					jhelp.closeConnection();
					jhelp = null;
					conn = null;
				}
				catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
			}
		}
		else
			return false;
		
		// TODO Auto-generated method stub
		
	}

	@Override
	public User authenticateUser(String email, String password)
	{	if(userExists(email.toLowerCase()))
		{	jhelp = new JDBCHelper();
			conn = jhelp.getConnection();
			
			String sql = "Select * from userinfo where email_Id = \'"+email.toLowerCase()+"\'";
			//System.out.println(sql);
			User user = null;
			try 
			{	stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while(rs.next())
				{	user = new User();
					user.setUserId(rs.getInt(1));
					user.setEmailId(rs.getString(2));
					user.setPassword(rs.getString(3));
					user.setFirstName(rs.getString(4));
					user.setLastName(rs.getString(5));
					//System.out.println("rs.getInt(6) : "+rs.getInt(6));
					user.setUserType(rs.getInt("user_type"));
					
				}	
				conn.commit();
				if(user.getPassword().equals(password))
					return user;
				else
					return null;
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				jhelp.closeConnection();
				jhelp = null;
				try 
				{
					stmt.close();
					stmt = null;
				}
				catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
			}
		
		}
		else
			return null;
	}

	@Override
	public boolean userExists(String emailId) 
	{	jhelp1 = new JDBCHelper();
		conn1 = jhelp1.getConnection();
		
		try
		{	String sql = "Select count(*) as userCount from userinfo where " +
						 "email_id = \'"+emailId.toLowerCase()+"\'";
			//System.out.println(sql);
			stmt = conn1.createStatement();
			rs = stmt.executeQuery(sql);
			int userCount = 0;
			while(rs.next())
			{
				userCount = rs.getInt("userCount");
			}
			if(userCount == 0)
				return false;
			else if(userCount > 0)
				return true;
			
		}
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();			
		}
		finally
		{	jhelp1.closeConnection();
			jhelp1 = null;
			try 
			{
				stmt.close();
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
			
		}
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean updateUser(User user) 
	{	//System.out.println("inside update user");
	
		if(userExists(user.getEmailId()) == true)
		{	jhelp = new JDBCHelper();
			conn = jhelp.getConnection();
			//System.out.println("inside update user");
			String sql = "Update userinfo set first_name = ?, last_name = ? ," +
					"user_type = ? where email_id = ?";
			try
			{	
				//System.out.println("sucess "+user+" "+sql);
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, user.getFirstName());
				pstmt.setString(2, user.getLastName());
				pstmt.setInt(3, user.getUserType());
				pstmt.setString(4, user.getEmailId());
				pstmt.executeUpdate();
				pstmt.close();
				conn.commit();
				
				//User user1 = authenticateUser(user.getEmailId(),user.getPassword());
				//System.out.println("inside user = "+user1);
				return true;
			}
			catch(SQLException sqlex)
			{
				sqlex.printStackTrace();
				return false;
			}
			finally
			{	sql = null;
				try 
				{
					pstmt.close();
					pstmt = null;
					jhelp.closeConnection();
					jhelp = null;
				}
				catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
				
		}
		else
			return false;
			
		// TODO Auto-generated method stub
		//return null;
	}
	
	

}
